package com.hotcomm.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

import org.junit.Before;
import org.junit.Test;

public class PostgresqlHelper {
	
    final String url="jdbc:postgresql://192.168.123.141:5432/hotcomm_v1";
    final String user="postgres";
    final String password = "137121";
	final static String className = "org.postgresql.Driver";
	List<Data> datas = new ArrayList<>();
	
	static {
		try {
			Class.forName(className);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	@Before
	public void readData() {
		for(int i=0;i<10000;i++) {
			String mac = UUID.randomUUID().toString().replace("-", "").substring(0, 19);
			String message = "{\"id\":\"1527041641116-TNCT5oqyUisBkwpnVuSvPkUY\",\"macAddr\":\"0000000022000014\",\"data\":\"b2e0001d0000e1\",\"recv\":\"2018-05-23T02:14:00.000Z\",\"extra\":{\"commsysType\":\"lora\",\"rssi\":-46,\"snr\":24.5,\"frameCnt\":29,\"gwid\":\"00001c497bca5a11\",\"gwip\":\"100.88.183.158\",\"channel\":470300000,\"sf\":12,\"fport\":1},\"pub\":\"2018-05-23T02:14:01.119Z\"}";
			datas.add(new Data(mac, message));
		}
	}
	
	@Test
	public void query() {
		PostgresqlHelper helper = new PostgresqlHelper();
		helper.queryData(10000,1707380);
	}
	
	@Test
	public void save() {
		long beginTimes = System.currentTimeMillis();
		saveData();
		long endTimes = System.currentTimeMillis();
		System.out.println("批量插入耗时"+(endTimes-beginTimes)+"毫秒");
	}
	
	public static void main(String[] args) {
		PostgresqlHelper helper = new PostgresqlHelper();
		helper.readData();
		int threadLengths = 100;
		long beginTimes = System.currentTimeMillis();
		helper.batchThreadData(threadLengths);
		long endTimes = System.currentTimeMillis();
		System.out.println("批量插入耗时"+(endTimes-beginTimes)+"毫秒");
	}
	
	
	public Connection getConn() {
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
	
	public void queryData(int limit,int offset) {
		long beginTimes = System.currentTimeMillis();
		final String sql = "select * from public.hk_data LIMIT "+limit+" offset "+offset+"";
		try(Connection connection = getConn();){
            System.out.println("是否成功连接pg数据库"+connection);
            List<Data> datas = new ArrayList<>();
            try(Statement statement=connection.createStatement();){
            	  ResultSet resultSet=statement.executeQuery(sql);
                  while(resultSet.next()){
                      String mac=resultSet.getString(1);
                      String message = resultSet.getString(2);
                      datas.add(new Data(mac, message));
                  }
          		long endTimes = System.currentTimeMillis();
        		System.out.println("查询结果数量"+1+",耗时"+(endTimes-beginTimes)+"毫秒");
                System.out.println("查询完毕");
            }catch(Exception e){
                throw new RuntimeException(e);
            }
        }catch(Exception e){
            throw new RuntimeException(e);
        }
	}
	
	public void saveData() {
		final String sql = "INSERT INTO public.hk_data(mac, message) VALUES (?, ?)";
		try(Connection connection = getConn();){
			System.out.println("是否成功连接pg数据库"+connection);
			for(Data data:datas) {
				 try(PreparedStatement statement=connection.prepareStatement(sql);){
					 statement.setString(1, data.getMac());
					 statement.setString(2, data.getMessage());
					 statement.executeUpdate();
	            }catch(Exception e){
	                throw new RuntimeException(e);
	            }
			}
		}catch(Exception e){
            throw new RuntimeException(e);
        }
		
	}

	public void batchSaveData() {
		final String sql = "INSERT INTO public.hk_data(mac, message) VALUES (?, ?)";
		try(Connection connection = getConn();){
			System.out.println("是否成功连接pg数据库"+connection);
			for(Data data:datas) {
				 try(PreparedStatement statement=connection.prepareStatement(sql);){
					 statement.setString(1, data.getMac());
					 statement.setString(2, data.getMessage());
					 statement.executeUpdate();
	            }catch(Exception e){
	                throw new RuntimeException(e);
	            }
			}
		}catch(Exception e){
            throw new RuntimeException(e);
        }
	}

	public void batchThreadData(int threadLengths) {
		 ExecutorService exService =Executors.newFixedThreadPool(threadLengths);
		 for (int i = 0; i<threadLengths; i++) {
	        	exService.execute(new Runnable() {
	        		
					@Override
					public void run() {
						saveData();
					}
				});
	        }
	}
	
	
}
